package com.lxyk.dao.statistic;


import com.github.pagehelper.PageInfo;
import com.lxyk.domain.StuAttendance;
import com.lxyk.domain.StuStatisticVO;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.security.core.parameters.P;

import java.util.Date;
import java.util.List;

//学生到课率
public interface StuAttendanceDao {

    //查询班级某一天所有学生到课数据
    @Select("select  a.name stdName,a.cid,b.* " +
            "from student a,stu_attendance b " +
            "where a.id=b.sid and a.cid=#{cid} and DATE_FORMAT(b.recordDate,'%Y-%m-%d')=DATE_FORMAT(#{recordDate},'%Y-%m-%d')" )
    List<StuStatisticVO> findClassDayAttendance(@Param("cid")Integer cid, @Param("recordDate")Date recordDate);

    //条件查询
    List<StuStatisticVO> findByCondition(StuStatisticVO stuStatisticVO);

    //更新到课率(批量)
    void update(List<StuStatisticVO> list);

    //修改日期和课程名
//    @Update("update stu_attendance set course=#{course},recordDate=#{newTime} " +
//            "where id in(select b.id from student a, stu_attendance b where a.id=b.sid and a.cid=#{cid} and b.recordDate=#{oldTime})")
//    void updateDateAndCourse(@Param("cid") Integer cid,@Param("oldTime") Date oldTime,@Param("newTime")Date newTime,@Param("course")String course);



    //根据班级id和日期查询学生信息
    @Select("select a.id sid,a.cid cid,a.name stdName,b.name course,b.attendTime recordDate " +
            "from student a,course_info b " +
            "where a.cid=b.cid  and a.cid=#{cid} and DATE_FORMAT(b.attendTime,'%Y-%m-%d')=DATE_FORMAT(#{recordDate},'%Y-%m-%d') and b.useFlag=1 " +
            "and a.id not in(select a.id from student a,  stu_attendance b where a.id=b.sid and a.cid=#{cid} and DATE_FORMAT(b.recordDate,'%Y-%m-%d')=DATE_FORMAT(#{recordDate},'%Y-%m-%d')) ")
    List<StuStatisticVO> findStudentByCidAndDate(@Param("cid") Integer cid, @Param("recordDate") Date recordDate);

    //添加到课率(批量)
    void add(List<StuStatisticVO> list);

    //查询最近一次到课率日期
    @Select("select  max(b.recordDate) " +
            "from student a,stu_attendance b " +
            "where a.id=b.sid and a.cid=#{cid}")
    Date getLastDate(Integer cid);


    //查询班级所有学生当天平均到课率
    //(1)查询班级当天实际到课学生人数
    @Select("select count(*) " +
            "from student a left join classes b on a.cid=b.id left join stu_attendance c on a.id=c.sid " +
            "where b.id=#{cid} and  DATE_FORMAT(c.recordDate,'%Y-%m-%d')=DATE_FORMAT(#{date},'%Y-%m-%d') " +
            "and c.status=1")
    int findRealAttendanceCount(@Param("cid") Integer cid,@Param("date") Date date);
//    //(2)查询班级当天应到课学生人数
//    @Select("select count(*) " +
//            "from student a left join classes b on a.cid=b.id left join stu_attendance c on a.id=c.sid " +
//            "where b.id=#{cid} and  DATE_FORMAT(c.recordDate,'%Y-%m-%d')=DATE_FORMAT(#{date},'%Y-%m-%d') ")
//    int findAllAttendanceCount(@Param("cid") Integer cid,@Param("date") Date date);
}
